﻿using UnityEngine;
using System;
using Mono.Data.Sqlite;

namespace UFrame
{
	public class SqliteDBAccess
	{
		private SqliteConnection m_connection;

		private SqliteCommand dbCommand;

		private SqliteDataReader reader;

		// Windows平台：data source=Application.dataPath/数据库名称.db
		// IOS平台：data source=Application.persistentDataPath/数据库名称.db
		// Android平台：URL=file:Application.persistentDataPath/数据库名称.db(我想说Android平台就是个奇葩。搞什么特殊化嘛)
		public bool OpenConnection(string connectionString, string password = null)
		{
			try
			{
				m_connection = new SqliteConnection(connectionString);
				if (!string.IsNullOrEmpty(password))
                    m_connection.SetPassword(password);
                m_connection.Open();
				Debug.Log("Connected to db");
				return true;
			}
			catch (Exception e)
			{
				string temp1 = e.ToString();
				Debug.Log(temp1);
				return false;
			}
		}

		public void CloseConnection()
		{
			if (dbCommand != null)
			{
				dbCommand.Dispose();
			}
			dbCommand = null;

			if (reader != null)
			{
				reader.Dispose();

			}
			reader = null;

			if (m_connection != null)
			{
				m_connection.Close();
			}
			m_connection = null;

			Debug.Log("Disconnected from db.");

		}

		public SqliteDataReader ExecuteQuery(string sqlQuery)
		{
            Debug.LogWarning(sqlQuery);

            try
            {
				dbCommand = m_connection.CreateCommand();

				dbCommand.CommandText = sqlQuery;

				reader = dbCommand.ExecuteReader();

                return reader;
            }
            catch (System.Exception ex)
			{
				Debug.LogException(ex);
				Debug.Log("[sqlQuery]:" + sqlQuery);
				return null;
			}
		}

		public SqliteDataReader ReadFullTable(string tableName)
		{
			string query = "SELECT * FROM " + tableName;

			return ExecuteQuery(query);
		}


		public SqliteDataReader InsertInto(string tableName, string[] values,bool replace = true)
		{
			string query = replace ? "INSERT OR REPLACE INTO " : "INSERT INTO ";
			query += tableName + " VALUES ('" + values[0] + "'";

			for (int i = 1; i < values.Length; ++i)
			{

				query += ", '" + values[i] + "'";

			}

			query += ")";
            return ExecuteQuery(query);
		}

		public SqliteDataReader UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
		{
			string query = "UPDATE " + tableName + " SET " + cols[0] + " = '" + colsvalues[0];

			for (int i = 1; i < colsvalues.Length; ++i)
			{

				query += "', " + cols[i] + " ='" + colsvalues[i];
			}

			query += "' WHERE " + selectkey + " = '" + selectvalue + "' ";

			return ExecuteQuery(query);
		}

		public void DeleteTable(string tableName)
		{
			string query = "DROP  table " + tableName;
			ExecuteQuery(query);
		}

		public SqliteDataReader Delete(string tableName, string[] cols, string[] colsvalues)
		{
			string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = '" + colsvalues[0];

			for (int i = 1; i < colsvalues.Length; ++i)
			{

				query += "' or " + cols[i] + " = '" + colsvalues[i];
			}
			query += "'"; 
			return ExecuteQuery(query);
		}


		public SqliteDataReader InsertIntoSpecific(string tableName, string[] cols, string[] values)
		{
			if (cols.Length != values.Length)
			{
				throw new SqliteException("columns.Length != values.Length");
			}

			string query = "INSERT INTO " + tableName + "(" + cols[0];

			for (int i = 1; i < cols.Length; ++i)
			{
				query += ", " + cols[i];
			}

			query += ") VALUES (" + values[0];

			for (int i = 1; i < values.Length; ++i)
			{
				query += ", " + values[i];
			}
			query += ")";
			return ExecuteQuery(query);
		}



		public SqliteDataReader DeleteContents(string tableName)
		{
			string query = "DELETE FROM " + tableName;

			return ExecuteQuery(query);
		}


		public SqliteDataReader CreateTable(string name, string[] col, string[] colType)
		{
			if (col.Length != colType.Length)
			{
				throw new SqliteException("columns.Length != colType.Length");
			}

			string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] + " PRIMARY KEY ";

			for (int i = 1; i < col.Length; ++i)
			{
				query += ", " + col[i] + " " + colType[i];
			}

			query += ")";
			Debug.Log("query " + query);
			return ExecuteQuery(query);
		}

		public bool IsExistTable(string tableName)
		{
			string cmd = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = '" + tableName + "';";
			SqliteDataReader sqReader = ExecuteQuery(cmd);
			bool result = false;

			while (sqReader != null && sqReader.Read())
			{
				int num = sqReader.GetInt32(0);
				if (num > 0) result = true;
			}

			return result;
		}

		public SqliteDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
		{
			if (col.Length != operation.Length || operation.Length != values.Length)
			{
				throw new SqliteException("col.Length != operation.Length != values.Length");
			}

			string query = "SELECT " + items[0];

			for (int i = 1; i < items.Length; ++i)
			{
				query += ", " + items[i];
			}

			query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
			for (int i = 1; i < col.Length; ++i)
			{
				query += " AND " + col[i] + operation[i] + "'" + values[i] + "' ";
			}
			return ExecuteQuery(query);
		}
	}
}